package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;
import ca.ualberta.cs.PrivacyManagement.Encryptor;
import ca.ualberta.cs.PrivacyManagement.Settings;

public class PersonsManagement extends EntityRelManagement {
	
	private static ArrayList<String> personDBFields = new ArrayList<String>();
    
	static FriendsManagement friendsMgmt = new FriendsManagement();
	static Settings settingsMgmt = new Settings();
	
	public PersonsManagement(){
		
		mainTableName = "Person";
		
		personDBFields.clear();
		//personId : generated automatically
   		personDBFields.add("username"); //personDBFields.add("firstName"); personDBFields.add("lastName");
   		personDBFields.add("name");
		personDBFields.add("email"); personDBFields.add("affiliation");
		personDBFields.add("password"); personDBFields.add("description");
	}
	
	public void initialize(Statement statement) throws SQLException{
		
		try{
			statement.executeUpdate("DROP TABLE Person");
		}catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE Person ( " +
				"ID INTEGER GENERATED ALWAYS AS " +
					"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
				"username VARCHAR(300), " +					
				//"firstName VARCHAR(300) NOT NULL, " +
				//"lastName VARCHAR(300) NOT NULL, " +
				"name VARCHAR(300) NOT NULL, " +
				"email VARCHAR(300), " + //TODO when inserting, should check if email is repeated AND username is not null: already exists, otherwise: update
				"affiliation VARCHAR(500), " +
				"password VARCHAR(50), " +
				"description VARCHAR(500), " +
				"joinDate Date," +					
				"CONSTRAINT id_uniq PRIMARY KEY(ID) " +
				//"CONSTRAINT user_uniq UNIQUE(username), " +
				//"CONSTRAINT email_uniq UNIQUE(email) " +
				") IN CLRSPACE INDEX IN CLRSPACE"); 
		
		numOfCols = 8;
		
		statement.execute("SET INTEGRITY FOR Person OFF");
		
		statement.executeUpdate("ALTER TABLE Person "+
				"ADD COLUMN pseudousername GENERATED ALWAYS AS " +
				"(CASE WHEN username IS NULL THEN ID END) " +
				"ADD COLUMN pseudoemail GENERATED ALWAYS AS " +
				"(CASE WHEN email IS NULL THEN ID END)");
		
		statement.executeUpdate("SET INTEGRITY FOR Person IMMEDIATE CHECKED FORCE GENERATED");
		
		statement.executeUpdate("create unique index u_pers_usern on Person(pseudousername, username)");
		statement.executeUpdate("create unique index u_pers_email on Person(pseudoemail, email)");
		
		settingsMgmt.initialize(statement);
		
		//statement.executeUpdate("CREATE UNIQUE WHERE NOT NULL INDEX uni_pers_usernam ON Person(username) ");
		//statement.executeUpdate("CREATE UNIQUE INDEX uni_pers_email ON Person(email) ");
	}
	
	//make it addPerson!!
	
	
	   /**
	    *@return -2 if email exists, -3 if an exception, personId if ok
	    */
	   public int addAuthor(User user){
		   int ret = -3;
		   	Connection con = DatabaseController.connectToDB(); 
		   	Statement statement;
				try {
					statement = con.createStatement();
					
					if(user.getEmail() != null ){
						ResultSet result = statement.executeQuery("SELECT ID FROM Person WHERE email = '"+ user.getEmail() + "'");
						
						if( result.next() ){ //email exists
							System.out.println("There is another account with this email address, please try again");
							statement.close();
							con.commit();
							return -2;				
						}
					}
					
					//INSERT
					String query = "INSERT INTO Person (";
					for(int i = 0 ; i < personDBFields.size() ; i++ ){
						if( personDBFields.get(i).equals("password") ){
							if(user.getPassword() != null)
								query += personDBFields.get(i) + "," ;
						}
						else if( user.getClass().getDeclaredField(personDBFields.get(i)).get(user) != null )
							query += personDBFields.get(i) + "," ;
					}
								
					query = query.substring(0, query.length()-1); //remove last ','
					//query += "joinDate";
					
					query += ") VALUES (";
					for(int i = 0 ; i < personDBFields.size() ; i++ ){
						if(personDBFields.get(i).equals("password")){
							if(user.getPassword() != null)
								query += "'"+user.getPassword() + "'," ;
							continue;
						}
						
						Object obj = user.getClass().getDeclaredField(personDBFields.get(i)).get(user);
						if( obj == null )
							//query += "NULL" +",";
							continue;
						else if( obj instanceof String )
							query += "'" + ((String)obj).replace("'", "`") + "',";
						else if( obj instanceof Integer )
							query += Integer.toString((Integer)obj) + ",";
						else if( obj instanceof Date )
							query += "'" + (new java.sql.Date(((Date)obj).getTime())).toString() +"'" + ","; 
								//((Date)obj).getYear() +"/" + ((Date)obj).getMonth() +"/" + ((Date)obj).getDay() + ","; //TODO
						else
							query += obj.toString() +"," ; //TODO
					}
					query = query.substring(0, query.length()-1); //remove last ','
//					query += "'" + (new java.sql.Date(new Date().getTime())).toString() +"'" ;
					query += ")";	
									
					statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
					ResultSet resKey = statement.getGeneratedKeys();
					resKey.next();
					ret = resKey.getInt(1);
									
					
					statement.close();
					con.commit();
					
				} catch (SQLException e) {
					System.err.println("SQLException:");
					while(e!=null) {
						System.err.println ("Error msg: " + e.getMessage());
						System.err.println ("SQLSTATE: " + e.getSQLState());
						System.err.println ("Error code: " + e.getErrorCode());
						e.printStackTrace();
						e = e.getNextException();
					}
					return -3;
				} catch (Exception e) {			
					e.printStackTrace();
					return -3;
				} 
		   	
		   	return ret;

	   }
	   
	   /**
	    * @param user
	    * @return 0: ok, return 1: username exists, return 2: email already taken, 3: exception
	    */
	   public int addUser(User user){    	
	   	
	   	Connection con = DatabaseController.connectToDB();
	   	Statement statement;
			try {
				statement = con.createStatement();		
				ResultSet result = statement.executeQuery("SELECT username FROM Person WHERE username = '"+ user.getUsername() + "'");
				
				if( result.next() ){ //username exists				
					System.out.println("Username is already taken, please try another username");
					statement.close();
					con.commit();
					return 1;				
				}
				
				result = statement.executeQuery("SELECT username FROM Person WHERE email = '" + user.getEmail() + "'" );
				
				if( result.next() ){ //email exists				
					System.out.println("There is another account with this email address, please try again");
					statement.close();
					con.commit();
					return 2;				
				}
				
				//INSERT
				String query = "INSERT INTO Person (";
				for(int i = 0 ; i < personDBFields.size() ; i++ )
					if(personDBFields.get(i).equals("password") && user.getPassword() != null )
						query += "password" + "," ;
					else if( user.getClass().getDeclaredField(personDBFields.get(i)).get(user) != null )
						query += personDBFields.get(i) + "," ;
							
				//query = query.substring(0, query.length()-1); //remove last ','
				query += "joinDate";
				
				query += ") VALUES (";
				for(int i = 0 ; i < personDBFields.size() ; i++ ){	
					if(personDBFields.get(i).equals("password")){
						if(user.getPassword() != null)
							query += "'"+user.getPassword() + "'," ;
						continue;
					}
					Object obj = user.getClass().getDeclaredField(personDBFields.get(i)).get(user);
					if( obj == null )
						//query += "NULL" +",";
						continue;
					else if( obj instanceof String )
						query += "'" + ((String)obj).replace("'", "`") + "',";
					else if( obj instanceof Integer )
						query += Integer.toString((Integer)obj) + ",";
					else if( obj instanceof Date )
						query += "'" + (new java.sql.Date(((Date)obj).getTime())).toString() +"'" + ","; 
							//((Date)obj).getYear() +"/" + ((Date)obj).getMonth() +"/" + ((Date)obj).getDay() + ","; //TODO
					else
						query += obj.toString() +"," ; //TODO
				}
				//query = query.substring(0, query.length()-1); //remove last ','
				query += "'" + (new java.sql.Date(new Date().getTime())).toString() +"'" ;
				query += ")";	
				
				statement.executeUpdate(query);
				statement.close();
				con.commit();
			
	 
				statement.close();
				con.commit();
				
			} catch (SQLException e) {
				System.err.println("SQLException:");
				while(e!=null) {
					System.err.println ("Error msg: " + e.getMessage());
					System.err.println ("SQLSTATE: " + e.getSQLState());
					System.err.println ("Error code: " + e.getErrorCode());
					e.printStackTrace();
					e = e.getNextException();
				}
				return 3;
			} catch (Exception e) {			
				e.printStackTrace();
				return 3;
			} 
	   	
	   	return 0;
	   }
	   
	   

	   /**
	    * @param username
	    * @param pass
	    * @return HashMap<String,String> where key: state, values is: 0: ok, 1: wrong pass, 2:no such user, 3: exception
	    */
	   	public HashMap<String,String> authenticate(String username, String pass){
	   		
	   		HashMap<String,String> ret = new HashMap<String, String>();
	   		
	   		if(username == null || username.length() == 0){
	   			ret.put("state", "2");
				return ret; //no such username   
	   		}
	   		
	   		Connection con = DatabaseController.connectToDB();
	   	   	Statement statement;
	   			try {
	   				statement = con.createStatement();		
	   				//System.out.println("anno ID:" +am.getId());		
	   				ResultSet result = statement.executeQuery("SELECT * FROM Person WHERE username = '"+ username +"'" );			
	   				
	   				if( result.next() ){

	   					if(!Encryptor.encryptedValsMatch(pass, result.getString("password"))){
	   						if(!pass.equalsIgnoreCase(result.getString("password")))
		   					{	   						
		   						ret.put("state", "1");
		   						return ret; //wrong password
		   					}
	   					}
	   					
	   					//ok:
	   					ret.put("state", "0");
	   					ret.put("name", result.getString("name"));
	   					ret.put("email", result.getString("email"));
	   					ret.put("aff", result.getString("affiliation"));
	   				}
	   				else{
	   					System.out.println("no such username...");
	   					ret.put("state", "2");
	   					return ret; //no such username   					
	   				}
	   				
	   			}catch (SQLException e) {
	   				System.err.println("SQLException:");
	   				while(e!=null) {
	   					System.err.println ("Error msg: " + e.getMessage());
	   					System.err.println ("SQLSTATE: " + e.getSQLState());
	   					System.err.println ("Error code: " + e.getErrorCode());
	   					e.printStackTrace();
	   					e = e.getNextException();
	   				}
	   				ret.put("state", "3");
	   				return ret;
	   			} catch (Exception e) {			
	   				e.printStackTrace();
	   				ret.put("state", "3");
	   				return ret;
	   			} 
	   		return ret; //ok
	   	}
	   	
	   	/**
		    * @param username
		    * @param pass encoded by SHA1
		    * @return HashMap<String,String> where key: state, values is: 0: ok, 1: wrong pass, 2:no such user, 3: exception
		    */
		   	public HashMap<String,String> authenticateEncoded(String username, String pass){
		   		
		   		HashMap<String,String> ret = new HashMap<String, String>();
		   		
		   		if(username == null || username.length() == 0){
		   			ret.put("state", "2");
					return ret; //no such username   
		   		}
		   		
		   		Connection con = DatabaseController.connectToDB();
		   	   	Statement statement;
		   			try {
		   				statement = con.createStatement();		
		   				//System.out.println("anno ID:" +am.getId());		
		   				ResultSet result = statement.executeQuery("SELECT * FROM Person WHERE username = '"+ username +"'" );			
		   				
		   				if( result.next() ){
		   					if(!pass.equalsIgnoreCase(result.getString("password"))){
			   					if(!Encryptor.encryptedValsMatch(pass, result.getString("password")))
			   					{
			   						ret.put("state", "1");
			   						return ret; //wrong password
			   					}
		   					}
		   					//ok:
		   					ret.put("state", "0");
		   					ret.put("name", result.getString("name"));
		   					ret.put("email", result.getString("email"));
		   					ret.put("aff", result.getString("affiliation"));
		   				}
		   				else{
		   					System.out.println("no such username...");
		   					ret.put("state", "2");
		   					return ret; //no such username   					
		   				}
		   				
		   			}catch (SQLException e) {
		   				DB2SqlException.printSqlErrorMsg(e);
		   				ret.put("state", "3");
		   				return ret;
		   			} catch (Exception e) {			
		   				e.printStackTrace();
		   				ret.put("state", "3");
		   				return ret;
		   			} 
		   		return ret; //ok
		   	}
		   	
	   	
		public int getPersonId(String username, Connection con){
			int ret = -1;
			boolean innerConnection = false;
			   
		   if(con == null){
			   con = DatabaseController.connectToDB();
			   innerConnection = true;
		   }
			   
	   	   	Statement statement;
	   			try {
	   				statement = con.createStatement();		
	   				ResultSet result = statement.executeQuery("SELECT * FROM Person WHERE username = '"+ username +"'" );			
	   				
	   				if( result.next() ){
	   					ret = result.getInt("ID");
	   				}
	   				
	   				statement.close();
	 			   if(innerConnection){
	 				   con.commit();
	 				   con.close();				   
	 			   }
	   			}catch (SQLException e) {
	   				DB2SqlException.printSqlErrorMsg(e,con,innerConnection);	   				
	   				return ret;
	   			} catch (Exception e) {			
	   				e.printStackTrace();
	   				DB2SqlException.closeConnection(con, innerConnection);
	   				return ret;
	   			} 
	   		return ret;
		}
		
		public void manageForgottenPasswordByUsername(String username){
			Connection con = DatabaseController.connectToDB();
	   	   	Statement statement;
	   			try {
	   				statement = con.createStatement();		
	   				//System.out.println("anno ID:" +am.getId());		
	   				ResultSet result = statement.executeQuery("SELECT email FROM Person WHERE username = '"+ username +"'" );			
	   				
	   				if( result.next() ){
	   					
	   				}
	   				
	   				statement.close();		 			   
 				   con.commit();
 				   con.close();	
		 				   
		   			}catch (SQLException e) {
		   				System.err.println("SQLException:");
		   				while(e!=null) {
		   					System.err.println ("Error msg: " + e.getMessage());
		   					System.err.println ("SQLSTATE: " + e.getSQLState());
		   					System.err.println ("Error code: " + e.getErrorCode());
		   					e.printStackTrace();
		   					e = e.getNextException();
		   				}	   						   			
		   			} catch (Exception e) {			
		   				e.printStackTrace();		   			
		   			}
		}
		
		public void manageForgottenPasswordByEmail(){
			
		}
		
		public FriendsManagement getFriendsManagement(){
			return friendsMgmt;
		}
}
